using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using DTOLayer;
using System.Data.SqlTypes;
using System.Data;


namespace DAOLayer
{
    public class TheDocGiaDAO:AbstractDao
    {
        public int TestExisted(TheDocGiaDTO dto)
        {
            int kq = -1;
            try
            {
                SqlConnection cn;
                cn = this.SqlConnectiondata();
                cn.Open();
                String str = "select count(*) from TheDocGia where mathedocgia = " + dto.MaTheDocGia;
                SqlCommand cmd = new SqlCommand(str, cn);
                if ((int)cmd.ExecuteScalar() == 0)
                    kq = 1;
                cn.Close();
                return kq;
            }
            catch (Exception e)
            {
                return kq;
            }
        }
        public int InsertTheDocGia(TheDocGiaDTO dto)
        {
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            int kq = TestExisted(dto);
            if (kq == -1)
                return -1;// Da ton tai the doc gia nay
            try
            {   
                cn.Open();
                //String str = "insert into TheDocGia (NgayLapThe,NgayHetHan,MaLoaiThe)  values (?,?,?)" + "";
                //SqlCommand cmd = new SqlCommand(str, cn);
                SqlCommand cmd = new SqlCommand("insert into TheDocGia (NgayLapThe,NgayHetHan,MaLoaiThe)  values (@NgayLapThe,@NgayHetHan,@MaLoaiThe)");
                cmd.Connection = cn;
                cmd.Parameters.Add(new SqlParameter("@NgayLapThe", SqlDbType.DateTime)).Value=dto.NgayLapThe;
             //   cmd.Parameters.Add("@NgayLapThe", SqlDbType.DateTime);
              //  cmd.Parameters["@NgayLapThe"].Value = dto.NgayLapThe;
                cmd.Parameters.Add(new SqlParameter("@NgayHetHan", SqlDbType.DateTime)).Value = dto.NgayHetHan;
      //          cmd.Parameters.Add("@NgayHetHan", SqlDbType.DateTime);
       //         cmd.Parameters["@NgayHetHan"].Value = dto.NgayHetHan;
                cmd.Parameters.Add(new SqlParameter("@MaLoaiThe", SqlDbType.Int)).Value = dto.MaLoaiThe;
                //cmd.Parameters.Add("@MaLoaiThe", SqlDbType.Int);
                //cmd.Parameters["@MaLoaiThe"].Value = dto.MaLoaiThe;
                cmd.ExecuteNonQuery();
                string str = "Select @@IDENTITY";
                cmd = new SqlCommand(str, cn);
                object obj =  cmd.ExecuteScalar();
                dto.MaTheDocGia = int.Parse(obj.ToString());
            }
            catch(Exception e)
            {
                return -1;
            }
            finally 
            {
                cn.Close();
            }
            return 1;
        }
        public int Delete (int mathedocgia)
        {
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            try
            {
                cn.Open();
                String str = "delete from TheDocGia where MaTheDocGia = ?";
                SqlCommand cmd = new SqlCommand(str,cn);
                cmd.Parameters.Add("@MaTheDocGia",SqlDbType.Int);
                cmd.Parameters["@MaTheDocGia"].Value = mathedocgia;
                cmd.ExecuteNonQuery();
                return 1;
            }
            catch(Exception e)
            {
                return -1;
            }
            finally
            {
                cn.Close();
            }
        }
        public int UpdateThedocGia(TheDocGiaDTO dto)
        {
            SqlConnection cn;
            cn = this.SqlConnectiondata();
            try
            {
                cn.Open();
                String str = "Update TheDocGia set NgayLapThe = ?, NgayHetHan = ?, MaLoaiThe = ? where MaTheDocGia = ?";
                SqlCommand cmd = new SqlCommand(str,cn);
                cmd.Parameters.Add("@NgayLapThe",SqlDbType.DateTime);
                cmd.Parameters["@NgayLapThe"].Value = dto.NgayLapThe;
                cmd.Parameters.Add("@NgayHetHan",SqlDbType.DateTime);
                cmd.Parameters["@NgayHetHan"].Value = dto.NgayHetHan;
                cmd.Parameters.Add("@MaLoaiThe",SqlDbType.Int);
                cmd.Parameters["@MaLoaiThe"].Value = dto.MaLoaiThe;
                cmd.Parameters.Add("@MaTheDocGia",SqlDbType.Int);
                cmd.Parameters["@MaTheDocGia"].Value = dto.MaTheDocGia;
                cmd.ExecuteNonQuery();
                return 1;
            }
            catch(Exception e)
            {
                return -1;
            }
            finally
            {
                cn.Close();
            }
        }
    }
}
